#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project ：tools 
@File ：excel数据匹配补全.py
@IDE  ：PyCharm 
@Author ：李涵彬
@Date ：2024/11/16 17:27 
"""

# 核心逻辑部分
import pandas as pd
from tkinter import filedialog, Tk, Label, Button, Listbox, MULTIPLE, END, messagebox


def load_excel(path):
	return pd.read_excel(path)


def save_result(df, path):
	df.to_csv(path, index=False)
	print(f"结果已保存至 {path}")


def complete_inventory(contract_df, inventory_df, match_fields, fill_fields):
	matched_rows = set()
	for index, row in inventory_df.iterrows():
		for field in fill_fields:
			if pd.isna(row[field]):  # 如果需要补全的字段为空
				matches = contract_df[contract_df[match_fields].eq(row[match_fields]).all(axis=1)]
				if len(matches) == 0:
					continue
				elif len(matches) == 1:
					matched_row = matches.iloc[0]
					if matched_row.name in matched_rows:
						continue
					inventory_df.at[index, field] = matched_row[field]
					matched_rows.add(matched_row.name)
				else:
					print(f"多条匹配记录，行索引：{index}, 匹配字段：{row[match_fields].to_dict()}")
					print(matches)
					choice = int(input("请输入选择的行号 (从0开始)："))
					matched_row = matches.iloc[choice]
					if matched_row.name in matched_rows:
						continue
					inventory_df.at[index, field] = matched_row[field]
					matched_rows.add(matched_row.name)
	return inventory_df


def main_logic(contract_path, inventory_path, match_fields, fill_fields, save_path):
	contract_df = load_excel(contract_path)
	inventory_df = load_excel(inventory_path)
	result_df = complete_inventory(contract_df, inventory_df, match_fields, fill_fields)
	save_result(result_df, save_path)


# GUI界面部分
def open_file_dialog():
	return filedialog.askopenfilename(filetypes=[("Excel Files", "*.xlsx")])


def select_fields(fields, title):
	selection_window = Tk()
	selection_window.title(title)

	selected = []

	def on_confirm():
		selected.extend([fields[i] for i in listbox.curselection()])
		selection_window.destroy()

	listbox = Listbox(selection_window, selectmode=MULTIPLE)
	for field in fields:
		listbox.insert(END, field)
	listbox.pack()

	confirm_button = Button(selection_window, text="确定", command=on_confirm)
	confirm_button.pack()

	selection_window.mainloop()
	return selected


def gui():
	root = Tk()
	root.title("数据匹配工具")

	contract_path, inventory_path = None, None

	def select_contract():
		nonlocal contract_path
		contract_path = open_file_dialog()
		contract_label.config(text=contract_path)

	def select_inventory():
		nonlocal inventory_path
		inventory_path = open_file_dialog()
		inventory_label.config(text=inventory_path)

	def start_processing():
		if not contract_path or not inventory_path:
			messagebox.showerror("错误", "请先选择文件路径")
			return

		contract_df = load_excel(contract_path)
		inventory_df = load_excel(inventory_path)

		match_fields = select_fields(list(contract_df.columns), "选择匹配字段")
		fill_fields = select_fields(list(inventory_df.columns), "选择需要补全的字段")

		if not match_fields or not fill_fields:
			messagebox.showerror("错误", "匹配字段或补全字段未选择")
			return

		save_path = filedialog.asksaveasfilename(defaultextension=".csv",
												 filetypes=[("CSV Files", "*.csv")])
		if not save_path:
			messagebox.showerror("错误", "未选择保存路径")
			return

		main_logic(contract_path, inventory_path, match_fields, fill_fields, save_path)
		messagebox.showinfo("完成", f"数据处理完成，结果已保存至 {save_path}")

	Label(root, text="选择合同表路径：").pack()
	contract_label = Label(root, text="")
	contract_label.pack()
	Button(root, text="选择", command=select_contract).pack()

	Label(root, text="选择库存表路径：").pack()
	inventory_label = Label(root, text="")
	inventory_label.pack()
	Button(root, text="选择", command=select_inventory).pack()

	Button(root, text="开始处理", command=start_processing).pack()
	root.mainloop()


if __name__ == "__main__":
	gui()
